package tsli.acm.dao;

import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;

public class PersistencyDAO {
	private Database db;
    

	 public PersistencyDAO(Database db) {
		 this.db = db;
	 }
	 
	 public List<Map<String, Object>> getAllAgentStructure() {
			String vaSql = "SELECT * "
					+ "FROM TSLI_PERSISTENCY";
			List<Map<String, Object>> result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
		}
	
	 public String getPersistencyModifyFOrMM(String paPeNoMM,String paClosYm) {
			String vaSql = "select NVL(TO_CHAR(SUM(Cal)/COUNT(Cal),'999.90'),'nul') AS PERSISTENCY_AMOUNT,CASE "
							        +" WHEN (SUM(Cal)/COUNT(Cal)) < 50 THEN 0.3 "
							        +" WHEN (SUM(Cal)/COUNT(Cal)) BETWEEN 50 AND 59 THEN 0.4 "
							        +" WHEN (SUM(Cal)/COUNT(Cal)) BETWEEN 60 AND 69 THEN 0.55 "
							        +" WHEN (SUM(Cal)/COUNT(Cal)) BETWEEN 70 AND 79 THEN 0.75 "
							        +" WHEN (SUM(Cal)/COUNT(Cal)) BETWEEN 80 AND 94 THEN 1 "
							        +" ELSE 1.1 "
							      +" END AS PERSISTENCY_MODIFY "
								+" from (select CASE "
							              +" WHEN SOLI_BIZRT = 0 OR PRST_BIZRT = 0 THEN 80 "
							            +" ELSE CAST(( PRST_BIZRT / SOLI_BIZRT ) * 100 as DECIMAL(6,2)) "
							            +" END AS Cal "
							      +" from TSLI_PERSISTENCY "
							      +" where PE_NO IN ( select PE_NO from TSLI_SFC_AGENT_STRUCTURE where PE_NO_MM = ?) "
										+" AND PRST_RTE_ITM_CD='S11030B10202'  AND CLOS_YM = ?) ";

			Map<String, Object> result = db.querySingle(vaSql, paPeNoMM , paClosYm);
			if (result != null) {
				return (String) result.get("PERSISTENCY_MODIFY").toString() +"/"+result.get("PERSISTENCY_AMOUNT");
			} else {
				return null;
			}
		}
	 
	 public String getPersisAndRatioForOE(String paClosYm, String paPeNoMM) {
			String vaSql = "SELECT  SUM(NVL(PRST_RTE,0)) AS PERSISTENCY , "
									+" CASE "
									+" WHEN SUM(NVL(PRST_RTE,0)) < 70 THEN 0.5 "
									+" WHEN SUM(NVL(PRST_RTE,0)) BETWEEN 70 AND 79 THEN 0.75 "
									+" ELSE 1 "
									+" END AS RATIO "
								    +" FROM (SELECT PE_NO,NVL(PRST_RTE,0) AS PRST_RTE "
								     		+" FROM TSLI_PERSISTENCY "
								     		+" WHERE PRST_RTE_ITM_CD = 'S11030B10202' AND clos_ym = ? AND PE_NO = ?) ";

			Map<String, Object> result = db.querySingle(vaSql, paClosYm, paPeNoMM);
			if (result != null) {
				return result.get("PERSISTENCY").toString()+"/"+(String) result.get("RATIO").toString();
			} else {
				return null;
			}
		}
}
